This notebook can be used to analyze payments by country and response code.

The database credentials will need to be passed on the command line as environment variables:

ECOMMERCE_REPLICA_DB_HOST="db.example.com" ECOMMERCE_REPLICA_DB_USER="db_user" ECOMMERCE_REPLICA_DB_PASSWORD="db_pass" jupyter notebook



In [ ]:

    
import os

import MySQLdb

db = MySQLdb.connect(
    host=os.environ['ECOMMERCE_REPLICA_DB_HOST'],
    database='ecommerce',
    user=os.environ['ECOMMERCE_REPLICA_DB_USER'],
    password=os.environ['ECOMMERCE_REPLICA_DB_PASSWORD']
)
cursor = db.cursor(cursorclass=MySQLdb.cursors.DictCursor)
cursor.execute('SELECT 1;')
print("Database connected!")



In [ ]:

    
cursor.execute("""
SELECT
  ppr.id,
  ppr.basket_id,
  u.username,
  ppr.response
FROM
  payment_paymentprocessorresponse ppr
  JOIN basket_basket b ON ppr.basket_id = b.id
  JOIN ecommerce_user u ON b.owner_id = u.id
WHERE
  ppr.created BETWEEN '2017-08-01' AND '2017-08-31'
  AND ppr.processor_name = 'cybersource'
""")



In [ ]:

    
import json
import pandas as pd

payments = []

for row in cursor:
    response = json.loads(row['response'])
    payments.append({
        'country': response.get('req_bill_to_address_country'),
        'accepted': 1 if response.get('decision') == 'ACCEPT' else 0,
        'reason_code': response.get('reason_code'),
        'amount': response.get('req_amount'),
    })

data = pd.DataFrame(payments)
data

Overall acceptance rate



In [ ]:

    
data['accepted'].mean()



In [ ]:

    
data.groupby('country') \
    .agg({'country': 'size', 'accepted': 'mean'}) \
    .rename(columns={'country': 'count', 'accepted': 'acceptance_rate'}) \
    .sort_values('count', ascending=False)



In [ ]:

    
# Reason code explanations at https://support.cybersource.com/cybskb/index?page=content&id=C156
reason_code_groups = data.groupby('reason_code').size().reset_index(name='counts').sort_values('counts', ascending=False)
reason_code_groups['percentage'] = reason_code_groups['counts'] / reason_code_groups['counts'].sum()
reason_code_groups



In [ ]:

    
indian_reason_code_groups = data.groupby('country').get_group('IN').groupby('reason_code').size().reset_index(name='counts').sort_values('counts', ascending=False)
indian_reason_code_groups['percentage'] = indian_reason_code_groups['counts'] / indian_reason_code_groups['counts'].sum()
indian_reason_code_groups



In [ ]:

    
usa_reason_code_groups = data.groupby('country').get_group('US').groupby('reason_code').size().reset_index(name='counts').sort_values('counts', ascending=False)
usa_reason_code_groups['percentage'] = usa_reason_code_groups['counts'] / usa_reason_code_groups['counts'].sum()
usa_reason_code_groups



In [ ]: